{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data preparation for tutorial\n",
    "\n",
    "This notebook contains the code to convert raw downloaded external data into a cleaned or simplified version for tutorial purposes.\n",
    "\n",
    "\n",
    "The raw data is expected to be in the `./raw` sub-directory (not included in the git repo)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "\n",
    "import pandas as pd\n",
    "import geopandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Countries dataset\n",
    "\n",
    "http://www.naturalearthdata.com/downloads/110m-cultural-vectors/110m-admin-0-countries/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries = geopandas.read_file(\"zip://./raw/original_data_ne/ne_110m_admin_0_countries.zip\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scalerank</th>\n",
       "      <th>featurecla</th>\n",
       "      <th>LABELRANK</th>\n",
       "      <th>SOVEREIGNT</th>\n",
       "      <th>SOV_A3</th>\n",
       "      <th>ADM0_DIF</th>\n",
       "      <th>LEVEL</th>\n",
       "      <th>TYPE</th>\n",
       "      <th>ADMIN</th>\n",
       "      <th>ADM0_A3</th>\n",
       "      <th>...</th>\n",
       "      <th>REGION_WB</th>\n",
       "      <th>NAME_LEN</th>\n",
       "      <th>LONG_LEN</th>\n",
       "      <th>ABBREV_LEN</th>\n",
       "      <th>TINY</th>\n",
       "      <th>HOMEPART</th>\n",
       "      <th>MIN_ZOOM</th>\n",
       "      <th>MIN_LABEL</th>\n",
       "      <th>MAX_LABEL</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Admin-0 country</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Sovereign country</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AFG</td>\n",
       "      <td>...</td>\n",
       "      <td>South Asia</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>POLYGON ((61.21081709172574 35.65007233330923,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Admin-0 country</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Sovereign country</td>\n",
       "      <td>Angola</td>\n",
       "      <td>AGO</td>\n",
       "      <td>...</td>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>6.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>(POLYGON ((23.90415368011818 -11.7222815894063...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Admin-0 country</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Sovereign country</td>\n",
       "      <td>Albania</td>\n",
       "      <td>ALB</td>\n",
       "      <td>...</td>\n",
       "      <td>Europe &amp; Central Asia</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>POLYGON ((21.0200403174764 40.84272695572588, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>Admin-0 country</td>\n",
       "      <td>4.0</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>ARE</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Sovereign country</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>ARE</td>\n",
       "      <td>...</td>\n",
       "      <td>Middle East &amp; North Africa</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>POLYGON ((51.57951867046327 24.24549713795111,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>Admin-0 country</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>ARG</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Sovereign country</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>ARG</td>\n",
       "      <td>...</td>\n",
       "      <td>Latin America &amp; Caribbean</td>\n",
       "      <td>9.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>-99.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>(POLYGON ((-66.95992000000001 -54.896810000000...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 72 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   scalerank       featurecla  LABELRANK            SOVEREIGNT SOV_A3  \\\n",
       "0          1  Admin-0 country        3.0           Afghanistan    AFG   \n",
       "1          1  Admin-0 country        3.0                Angola    AGO   \n",
       "2          1  Admin-0 country        6.0               Albania    ALB   \n",
       "3          1  Admin-0 country        4.0  United Arab Emirates    ARE   \n",
       "4          1  Admin-0 country        2.0             Argentina    ARG   \n",
       "\n",
       "   ADM0_DIF  LEVEL               TYPE                 ADMIN ADM0_A3  \\\n",
       "0       0.0    2.0  Sovereign country           Afghanistan     AFG   \n",
       "1       0.0    2.0  Sovereign country                Angola     AGO   \n",
       "2       0.0    2.0  Sovereign country               Albania     ALB   \n",
       "3       0.0    2.0  Sovereign country  United Arab Emirates     ARE   \n",
       "4       0.0    2.0  Sovereign country             Argentina     ARG   \n",
       "\n",
       "                         ...                          \\\n",
       "0                        ...                           \n",
       "1                        ...                           \n",
       "2                        ...                           \n",
       "3                        ...                           \n",
       "4                        ...                           \n",
       "\n",
       "                    REGION_WB NAME_LEN LONG_LEN  ABBREV_LEN  TINY HOMEPART  \\\n",
       "0                  South Asia     11.0     11.0         4.0 -99.0      1.0   \n",
       "1          Sub-Saharan Africa      6.0      6.0         4.0 -99.0      1.0   \n",
       "2       Europe & Central Asia      7.0      7.0         4.0 -99.0      1.0   \n",
       "3  Middle East & North Africa     20.0     20.0         6.0 -99.0      1.0   \n",
       "4   Latin America & Caribbean      9.0      9.0         4.0 -99.0      1.0   \n",
       "\n",
       "   MIN_ZOOM MIN_LABEL MAX_LABEL  \\\n",
       "0       0.0       3.0       7.0   \n",
       "1       0.0       3.0       7.0   \n",
       "2       0.0       5.0      10.0   \n",
       "3       0.0       4.0       9.0   \n",
       "4       0.0       2.0       7.0   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((61.21081709172574 35.65007233330923,...  \n",
       "1  (POLYGON ((23.90415368011818 -11.7222815894063...  \n",
       "2  POLYGON ((21.0200403174764 40.84272695572588, ...  \n",
       "3  POLYGON ((51.57951867046327 24.24549713795111,...  \n",
       "4  (POLYGON ((-66.95992000000001 -54.896810000000...  \n",
       "\n",
       "[5 rows x 72 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "177"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(countries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_subset = countries[['ADM0_A3', 'NAME', 'CONTINENT', 'POP_EST', 'GDP_MD_EST', 'geometry']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_subset.columns = countries_subset.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_subset = countries_subset.rename(columns={'adm0_a3': 'iso_a3'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>iso_a3</th>\n",
       "      <th>name</th>\n",
       "      <th>continent</th>\n",
       "      <th>pop_est</th>\n",
       "      <th>gdp_md_est</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AFG</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Asia</td>\n",
       "      <td>34124811.0</td>\n",
       "      <td>64080.0</td>\n",
       "      <td>POLYGON ((61.21081709172574 35.65007233330923,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AGO</td>\n",
       "      <td>Angola</td>\n",
       "      <td>Africa</td>\n",
       "      <td>29310273.0</td>\n",
       "      <td>189000.0</td>\n",
       "      <td>(POLYGON ((23.90415368011818 -11.7222815894063...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ALB</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Europe</td>\n",
       "      <td>3047987.0</td>\n",
       "      <td>33900.0</td>\n",
       "      <td>POLYGON ((21.0200403174764 40.84272695572588, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ARE</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Asia</td>\n",
       "      <td>6072475.0</td>\n",
       "      <td>667200.0</td>\n",
       "      <td>POLYGON ((51.57951867046327 24.24549713795111,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ARG</td>\n",
       "      <td>Argentina</td>\n",
       "      <td>South America</td>\n",
       "      <td>44293293.0</td>\n",
       "      <td>879400.0</td>\n",
       "      <td>(POLYGON ((-66.95992000000001 -54.896810000000...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  iso_a3                  name      continent     pop_est  gdp_md_est  \\\n",
       "0    AFG           Afghanistan           Asia  34124811.0     64080.0   \n",
       "1    AGO                Angola         Africa  29310273.0    189000.0   \n",
       "2    ALB               Albania         Europe   3047987.0     33900.0   \n",
       "3    ARE  United Arab Emirates           Asia   6072475.0    667200.0   \n",
       "4    ARG             Argentina  South America  44293293.0    879400.0   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((61.21081709172574 35.65007233330923,...  \n",
       "1  (POLYGON ((23.90415368011818 -11.7222815894063...  \n",
       "2  POLYGON ((21.0200403174764 40.84272695572588, ...  \n",
       "3  POLYGON ((51.57951867046327 24.24549713795111,...  \n",
       "4  (POLYGON ((-66.95992000000001 -54.896810000000...  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "countries_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "countries_subset.to_file(\"ne_110m_admin_0_countries.shp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Natural Earth - Cities dataset\n",
    "\n",
    "http://www.naturalearthdata.com/downloads/110m-cultural-vectors/110m-populated-places/ (simple, version 4.0.0, downloaded May 2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = geopandas.read_file(\"zip://./raw/original_data_ne/ne_110m_populated_places_simple.zip\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scalerank</th>\n",
       "      <th>natscale</th>\n",
       "      <th>labelrank</th>\n",
       "      <th>featurecla</th>\n",
       "      <th>name</th>\n",
       "      <th>namepar</th>\n",
       "      <th>namealt</th>\n",
       "      <th>diffascii</th>\n",
       "      <th>nameascii</th>\n",
       "      <th>adm0cap</th>\n",
       "      <th>...</th>\n",
       "      <th>pop_other</th>\n",
       "      <th>rank_max</th>\n",
       "      <th>rank_min</th>\n",
       "      <th>geonameid</th>\n",
       "      <th>meganame</th>\n",
       "      <th>ls_name</th>\n",
       "      <th>ls_match</th>\n",
       "      <th>checkme</th>\n",
       "      <th>min_zoom</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>8</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>Admin-0 capital</td>\n",
       "      <td>Vatican City</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>Vatican City</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>562430</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>6691831.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Vatican City</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>POINT (12.45338654497177 41.90328217996012)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>Admin-0 capital</td>\n",
       "      <td>San Marino</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>San Marino</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>3168070.0</td>\n",
       "      <td>None</td>\n",
       "      <td>San Marino</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6.1</td>\n",
       "      <td>POINT (12.44177015780014 43.936095834768)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>Admin-0 capital</td>\n",
       "      <td>Vaduz</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>Vaduz</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>33009</td>\n",
       "      <td>7</td>\n",
       "      <td>5</td>\n",
       "      <td>3042030.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Vaduz</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6.7</td>\n",
       "      <td>POINT (9.516669472907267 47.13372377429357)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>8</td>\n",
       "      <td>Admin-0 capital alt</td>\n",
       "      <td>Lobamba</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>Lobamba</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>935048.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Lobamba</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "      <td>POINT (31.19999710971274 -26.46666746135247)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>30</td>\n",
       "      <td>8</td>\n",
       "      <td>Admin-0 capital</td>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>106219</td>\n",
       "      <td>9</td>\n",
       "      <td>8</td>\n",
       "      <td>2960316.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>POINT (6.130002806227083 49.61166037912108)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 38 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   scalerank  natscale  labelrank           featurecla          name namepar  \\\n",
       "0          8        10          3      Admin-0 capital  Vatican City    None   \n",
       "1          7        20          0      Admin-0 capital    San Marino    None   \n",
       "2          7        20          0      Admin-0 capital         Vaduz    None   \n",
       "3          6        30          8  Admin-0 capital alt       Lobamba    None   \n",
       "4          6        30          8      Admin-0 capital    Luxembourg    None   \n",
       "\n",
       "  namealt  diffascii     nameascii  adm0cap  \\\n",
       "0    None          0  Vatican City      1.0   \n",
       "1    None          0    San Marino      1.0   \n",
       "2    None          0         Vaduz      1.0   \n",
       "3    None          0       Lobamba      0.0   \n",
       "4    None          0    Luxembourg      1.0   \n",
       "\n",
       "                       ...                       pop_other rank_max  rank_min  \\\n",
       "0                      ...                          562430        2         2   \n",
       "1                      ...                               0        7         7   \n",
       "2                      ...                           33009        7         5   \n",
       "3                      ...                               0        5         4   \n",
       "4                      ...                          106219        9         8   \n",
       "\n",
       "   geonameid meganame       ls_name ls_match checkme min_zoom  \\\n",
       "0  6691831.0     None  Vatican City        1       0      7.0   \n",
       "1  3168070.0     None    San Marino        1       5      6.1   \n",
       "2  3042030.0     None         Vaduz        1       0      6.7   \n",
       "3   935048.0     None       Lobamba        1       5      6.0   \n",
       "4  2960316.0     None    Luxembourg        1       0      6.0   \n",
       "\n",
       "                                       geometry  \n",
       "0   POINT (12.45338654497177 41.90328217996012)  \n",
       "1     POINT (12.44177015780014 43.936095834768)  \n",
       "2   POINT (9.516669472907267 47.13372377429357)  \n",
       "3  POINT (31.19999710971274 -26.46666746135247)  \n",
       "4   POINT (6.130002806227083 49.61166037912108)  \n",
       "\n",
       "[5 rows x 38 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "243"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_subset = cities[['name', 'geometry']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vatican City</td>\n",
       "      <td>POINT (12.45338654497177 41.90328217996012)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>San Marino</td>\n",
       "      <td>POINT (12.44177015780014 43.936095834768)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Vaduz</td>\n",
       "      <td>POINT (9.516669472907267 47.13372377429357)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lobamba</td>\n",
       "      <td>POINT (31.19999710971274 -26.46666746135247)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>POINT (6.130002806227083 49.61166037912108)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name                                      geometry\n",
       "0  Vatican City   POINT (12.45338654497177 41.90328217996012)\n",
       "1    San Marino     POINT (12.44177015780014 43.936095834768)\n",
       "2         Vaduz   POINT (9.516669472907267 47.13372377429357)\n",
       "3       Lobamba  POINT (31.19999710971274 -26.46666746135247)\n",
       "4    Luxembourg   POINT (6.130002806227083 49.61166037912108)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cities_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "cities_subset.to_file(\"ne_110m_populated_places.shp\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Natural Earth - Rivers dataset\n",
    "\n",
    "http://www.naturalearthdata.com/downloads/50m-physical-vectors/50m-rivers-lake-centerlines/ (version 4.0.0, downloaded May 2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers = geopandas.read_file(\"zip://./raw/ne_50m_rivers_lake_centerlines.zip\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scalerank</th>\n",
       "      <th>featurecla</th>\n",
       "      <th>name</th>\n",
       "      <th>note</th>\n",
       "      <th>min_zoom</th>\n",
       "      <th>name_alt</th>\n",
       "      <th>name_en</th>\n",
       "      <th>min_label</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6</td>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Kama</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Kama</td>\n",
       "      <td>6.0</td>\n",
       "      <td>LINESTRING (51.9371337598152 55.70106609892139...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>River</td>\n",
       "      <td>Kama</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Kama</td>\n",
       "      <td>6.0</td>\n",
       "      <td>LINESTRING (53.69384765584471 58.2063174502901...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Abay</td>\n",
       "      <td>None</td>\n",
       "      <td>3.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Abay</td>\n",
       "      <td>4.0</td>\n",
       "      <td>LINESTRING (37.11301150887408 11.8549872909308...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Al Furat</td>\n",
       "      <td>None</td>\n",
       "      <td>3.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Al Furat</td>\n",
       "      <td>4.0</td>\n",
       "      <td>LINESTRING (38.56119184742585 35.8626433379197...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>None</td>\n",
       "      <td>5.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>6.0</td>\n",
       "      <td>(LINESTRING (-86.52176754393696 33.03211843501...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   scalerank       featurecla      name  note  min_zoom name_alt   name_en  \\\n",
       "0          6  Lake Centerline      Kama  None       5.0     None      Kama   \n",
       "1          6            River      Kama  None       5.0     None      Kama   \n",
       "2          3  Lake Centerline      Abay  None       3.0     None      Abay   \n",
       "3          3  Lake Centerline  Al Furat  None       3.0     None  Al Furat   \n",
       "4          6  Lake Centerline   Alabama  None       5.0     None   Alabama   \n",
       "\n",
       "   min_label                                           geometry  \n",
       "0        6.0  LINESTRING (51.9371337598152 55.70106609892139...  \n",
       "1        6.0  LINESTRING (53.69384765584471 58.2063174502901...  \n",
       "2        4.0  LINESTRING (37.11301150887408 11.8549872909308...  \n",
       "3        4.0  LINESTRING (38.56119184742585 35.8626433379197...  \n",
       "4        6.0  (LINESTRING (-86.52176754393696 33.03211843501...  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rivers.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove rows with missing geometry:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "462"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rivers)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers = rivers[~rivers.geometry.isna()].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "461"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(rivers)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Subset of the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers_subset = rivers[['featurecla', 'name_en', 'geometry']].rename(columns={'name_en': 'name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>featurecla</th>\n",
       "      <th>name</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Kama</td>\n",
       "      <td>LINESTRING (51.9371337598152 55.70106609892139...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>River</td>\n",
       "      <td>Kama</td>\n",
       "      <td>LINESTRING (53.69384765584471 58.2063174502901...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Abay</td>\n",
       "      <td>LINESTRING (37.11301150887408 11.8549872909308...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Al Furat</td>\n",
       "      <td>LINESTRING (38.56119184742585 35.8626433379197...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lake Centerline</td>\n",
       "      <td>Alabama</td>\n",
       "      <td>(LINESTRING (-86.52176754393696 33.03211843501...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        featurecla      name  \\\n",
       "0  Lake Centerline      Kama   \n",
       "1            River      Kama   \n",
       "2  Lake Centerline      Abay   \n",
       "3  Lake Centerline  Al Furat   \n",
       "4  Lake Centerline   Alabama   \n",
       "\n",
       "                                            geometry  \n",
       "0  LINESTRING (51.9371337598152 55.70106609892139...  \n",
       "1  LINESTRING (53.69384765584471 58.2063174502901...  \n",
       "2  LINESTRING (37.11301150887408 11.8549872909308...  \n",
       "3  LINESTRING (38.56119184742585 35.8626433379197...  \n",
       "4  (LINESTRING (-86.52176754393696 33.03211843501...  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rivers_subset.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "rivers_subset.to_file(\"ne_50m_rivers_lake_centerlines.shp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Paris districts"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source: https://opendata.paris.fr/explore/dataset/quartier_paris/ (downloaded as GeoJSON file on August 20, 2018)\n",
    "\n",
    "Administrative districts, polygon dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts = geopandas.read_file(\"./raw/quartier_paris.geojson\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>n_sq_qu</th>\n",
       "      <th>perimetre</th>\n",
       "      <th>objectid</th>\n",
       "      <th>longueur</th>\n",
       "      <th>c_qu</th>\n",
       "      <th>surface</th>\n",
       "      <th>n_sq_ar</th>\n",
       "      <th>c_quinsee</th>\n",
       "      <th>l_qu</th>\n",
       "      <th>c_ar</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>750000010</td>\n",
       "      <td>2139.625388</td>\n",
       "      <td>50</td>\n",
       "      <td>2139.535591</td>\n",
       "      <td>10</td>\n",
       "      <td>271750.323937</td>\n",
       "      <td>750000003</td>\n",
       "      <td>7510302</td>\n",
       "      <td>Enfants-Rouges</td>\n",
       "      <td>3</td>\n",
       "      <td>POLYGON ((2.367101341254551 48.86162755885409,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>750000016</td>\n",
       "      <td>3283.163371</td>\n",
       "      <td>56</td>\n",
       "      <td>3282.999717</td>\n",
       "      <td>16</td>\n",
       "      <td>378252.153674</td>\n",
       "      <td>750000004</td>\n",
       "      <td>7510404</td>\n",
       "      <td>Notre-Dame</td>\n",
       "      <td>4</td>\n",
       "      <td>POLYGON ((2.361313701339139 48.84858030437791,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>750000018</td>\n",
       "      <td>4052.729521</td>\n",
       "      <td>58</td>\n",
       "      <td>4052.473226</td>\n",
       "      <td>18</td>\n",
       "      <td>798389.398463</td>\n",
       "      <td>750000005</td>\n",
       "      <td>7510502</td>\n",
       "      <td>Jardin-des-Plantes</td>\n",
       "      <td>5</td>\n",
       "      <td>POLYGON ((2.364561460891576 48.84365746114398,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>750000025</td>\n",
       "      <td>3827.253353</td>\n",
       "      <td>7</td>\n",
       "      <td>3827.053421</td>\n",
       "      <td>25</td>\n",
       "      <td>826559.436780</td>\n",
       "      <td>750000007</td>\n",
       "      <td>7510701</td>\n",
       "      <td>Saint-Thomas-d'Aquin</td>\n",
       "      <td>7</td>\n",
       "      <td>POLYGON ((2.322133508640103 48.84924973446431,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>750000039</td>\n",
       "      <td>3245.891413</td>\n",
       "      <td>21</td>\n",
       "      <td>3245.778222</td>\n",
       "      <td>39</td>\n",
       "      <td>609034.654451</td>\n",
       "      <td>750000010</td>\n",
       "      <td>7511003</td>\n",
       "      <td>Porte-Saint-Martin</td>\n",
       "      <td>10</td>\n",
       "      <td>POLYGON ((2.363917183048105 48.86754108728465,...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     n_sq_qu    perimetre  objectid     longueur  c_qu        surface  \\\n",
       "0  750000010  2139.625388        50  2139.535591    10  271750.323937   \n",
       "1  750000016  3283.163371        56  3282.999717    16  378252.153674   \n",
       "2  750000018  4052.729521        58  4052.473226    18  798389.398463   \n",
       "3  750000025  3827.253353         7  3827.053421    25  826559.436780   \n",
       "4  750000039  3245.891413        21  3245.778222    39  609034.654451   \n",
       "\n",
       "     n_sq_ar  c_quinsee                  l_qu  c_ar  \\\n",
       "0  750000003    7510302        Enfants-Rouges     3   \n",
       "1  750000004    7510404            Notre-Dame     4   \n",
       "2  750000005    7510502    Jardin-des-Plantes     5   \n",
       "3  750000007    7510701  Saint-Thomas-d'Aquin     7   \n",
       "4  750000010    7511003    Porte-Saint-Martin    10   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((2.367101341254551 48.86162755885409,...  \n",
       "1  POLYGON ((2.361313701339139 48.84858030437791,...  \n",
       "2  POLYGON ((2.364561460891576 48.84365746114398,...  \n",
       "3  POLYGON ((2.322133508640103 48.84924973446431,...  \n",
       "4  POLYGON ((2.363917183048105 48.86754108728465,...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "districts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts = districts.rename(columns={'l_qu': 'district_name', 'c_qu': 'id'}).sort_values('id').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Add population data (based on pdfs downloaded from ..):"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import camelot\n",
    "\n",
    "import glob\n",
    "files = glob.glob(\"../../Downloads/A_*.pdf\")\n",
    "\n",
    "\n",
    "results = []\n",
    "\n",
    "for fname in files:\n",
    "    print(fname)\n",
    "    tables = camelot.read_pdf(fname, pages='17-end', flavor='stream')\n",
    "\n",
    "    for t in tables:\n",
    "\n",
    "        df = t.df\n",
    "        if df.loc[0, 0] == \"1.  SUPERFICIES ET DENSITÉS EN 1999\":\n",
    "            district_name = df.loc[2, 0]\n",
    "            assert df.loc[10, 0] == 'POPULATION TOTALE EN 1999'\n",
    "            population = df.loc[10, 1]\n",
    "            print(t.page)\n",
    "            print(district_name)\n",
    "            results.append([district_name, population])\n",
    "\n",
    "df = pd.DataFrame(results, columns=['district_name', 'population'])                                                                                                   \n",
    "df['population'] = df['population'].str.replace(' ', '').astype('int64')                                                                                              \n",
    "df.to_csv(\"datasets/paris-population.csv\", index=False)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "population = pd.read_csv(\"./raw/paris-population.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "population['temp'] = population.district_name.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "population['temp'] = population['temp'].replace({\n",
    "    'javel': 'javel 15art',\n",
    "    'saint avoye': 'sainte avoie',\n",
    "    \"saint germain l'auxerrois\": \"st germain l'auxerrois\",\n",
    "    'plaine monceau': 'plaine de monceaux',\n",
    "    'la   chapelle': 'la chapelle'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts['temp'] = (districts.district_name.str.lower().str.replace('-', ' ')\n",
    "                              .str.replace('é', 'e').str.replace('è', 'e').str.replace('ê', 'e').str.replace('ô', 'o'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "res = pd.merge(districts, population[['population', 'temp']], on='temp', how='outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(res) == len(districts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts = res[['id', 'district_name', 'population', 'geometry']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>district_name</th>\n",
       "      <th>population</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>St-Germain-l'Auxerrois</td>\n",
       "      <td>1672</td>\n",
       "      <td>POLYGON ((2.344593389828428 48.85404991486192,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Halles</td>\n",
       "      <td>8984</td>\n",
       "      <td>POLYGON ((2.349365804803003 48.86057567227663,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Palais-Royal</td>\n",
       "      <td>3195</td>\n",
       "      <td>POLYGON ((2.339465868602756 48.86213531210705,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Place-Vendôme</td>\n",
       "      <td>3044</td>\n",
       "      <td>POLYGON ((2.331944969393234 48.86491285292422,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gaillon</td>\n",
       "      <td>1345</td>\n",
       "      <td>POLYGON ((2.336320212305949 48.8679713890312, ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id           district_name  population  \\\n",
       "0   1  St-Germain-l'Auxerrois        1672   \n",
       "1   2                  Halles        8984   \n",
       "2   3            Palais-Royal        3195   \n",
       "3   4           Place-Vendôme        3044   \n",
       "4   5                 Gaillon        1345   \n",
       "\n",
       "                                            geometry  \n",
       "0  POLYGON ((2.344593389828428 48.85404991486192,...  \n",
       "1  POLYGON ((2.349365804803003 48.86057567227663,...  \n",
       "2  POLYGON ((2.339465868602756 48.86213531210705,...  \n",
       "3  POLYGON ((2.331944969393234 48.86491285292422,...  \n",
       "4  POLYGON ((2.336320212305949 48.8679713890312, ...  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "districts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts.to_file(\"processed/paris_districts.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts = districts.to_crs(epsg=32631)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "districts.to_file(\"paris_districts_utm.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Commerces de Paris"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Source: https://opendata.paris.fr/explore/dataset/commercesparis/ (downloaded as csv file (`commercesparis.csv`) on October 30, 2018)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/joris/miniconda3/lib/python3.5/site-packages/IPython/core/interactiveshell.py:2698: DtypeWarning: Columns (10) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv(\"./raw/commercesparis.csv\", sep=';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ORDRE                              2379\n",
       "ARRONDISSEMENT                    75001\n",
       "QUARTIER                              3\n",
       "IRIS                         7.5101e+08\n",
       "ILOT                         7.5101e+10\n",
       "NUMERO                                3\n",
       "LET                                 NaN\n",
       "TYPE VOIE                           RUE\n",
       "LIBELLE VOIE                  PYRAMIDES\n",
       "ADRESSE COMPLETE        3 RUE PYRAMIDES\n",
       "CFA                                7902\n",
       "X                                651020\n",
       "Y                           6.86291e+06\n",
       "XY                  48.864299, 2.332401\n",
       "SEQUENCE                              3\n",
       "SITUATION                       Sur rue\n",
       "CODE ACTIVITE                     CH106\n",
       "LIBELLE ACTIVITE    Restaurant européen\n",
       "SURFACE                               1\n",
       "CC ID                               NaN\n",
       "CC NIV                              NaN\n",
       "Name: 0, dtype: object"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Take subset of the restaurants:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants = df[df['CODE ACTIVITE'].str.startswith('CH1', na=False)].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Restaurant traditionnel français                  1947\n",
       "Restaurant asiatique                              1643\n",
       "Restaurant européen                               1178\n",
       "Restaurant indien, pakistanais et Moyen Orient     394\n",
       "Restaurant maghrébin                               207\n",
       "Restaurant africain                                138\n",
       "Autre restaurant du monde                          107\n",
       "Restaurant central et sud américain                 97\n",
       "Restaurant antillais                                27\n",
       "Name: LIBELLE ACTIVITE, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants['LIBELLE ACTIVITE'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants = restaurants.dropna(subset=['XY']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Translate the restaurants and rename column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants['LIBELLE ACTIVITE'] = restaurants['LIBELLE ACTIVITE'].replace({\n",
    "    'Restaurant traditionnel français': 'Traditional French restaurant',\n",
    "    'Restaurant asiatique': 'Asian restaurant',\n",
    "    'Restaurant européen': 'European restuarant',\n",
    "    'Restaurant indien, pakistanais et Moyen Orient': 'Indian / Middle Eastern restaurant',\n",
    "    'Restaurant maghrébin': 'Maghrebian restaurant',\n",
    "    'Restaurant africain': 'African restaurant',\n",
    "    'Autre restaurant du monde': 'Other world restaurant',\n",
    "    'Restaurant central et sud américain': 'Central and South American restuarant',\n",
    "    'Restaurant antillais': 'Caribbean restaurant'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants = restaurants.rename(columns={'LIBELLE ACTIVITE': 'type'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create GeoDataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "from shapely.geometry import Point"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants['geometry'] = restaurants['XY'].str.split(', ').map(lambda x: Point(float(x[1]), float(x[0])))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants = geopandas.GeoDataFrame(restaurants[['type', 'geometry']], crs={'init': 'epsg:4326'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>geometry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>European restuarant</td>\n",
       "      <td>POINT (2.332401 48.864299)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Traditional French restaurant</td>\n",
       "      <td>POINT (2.331778 48.86526)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Traditional French restaurant</td>\n",
       "      <td>POINT (2.332541 48.865932)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Indian / Middle Eastern restaurant</td>\n",
       "      <td>POINT (2.332785 48.866285)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Traditional French restaurant</td>\n",
       "      <td>POINT (2.332008 48.866444)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 type                    geometry\n",
       "0                 European restuarant  POINT (2.332401 48.864299)\n",
       "1       Traditional French restaurant   POINT (2.331778 48.86526)\n",
       "2       Traditional French restaurant  POINT (2.332541 48.865932)\n",
       "3  Indian / Middle Eastern restaurant  POINT (2.332785 48.866285)\n",
       "4       Traditional French restaurant  POINT (2.332008 48.866444)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restaurants.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "restaurants.to_file(\"processed/paris_restaurants.gpkg\", driver='GPKG')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
